{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "90023b29-6e4d-4654-9c3d-8f8b0e5f0fca",
   "metadata": {},
   "source": [
    "## B01_Descriptive Analysis and Dashboarding\n",
    "\n",
    "This code implements exploratory data analysis and interactive dashboards."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c67ec235-bb44-43bb-b6aa-32e3c6887b68",
   "metadata": {},
   "source": [
    "### 1. init spark session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "bd7ad00e-1b59-4dc1-897e-97074b25a883",
   "metadata": {},
   "outputs": [],
   "source": [
    "import findspark\n",
    "findspark.init()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4e3ce44a-50dc-42b0-a116-edba2a567ae9",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "spark = SparkSession.builder.appName(\"price_opendata\").getOrCreate()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c417c08c-b6d6-4c1f-b4a8-abdb1b05de2d",
   "metadata": {},
   "source": [
    "### 2. read the parquet file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "bcd1f0e3-b6eb-4f2b-aa9f-309b342e26cc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+-----------+--------------+----------------+------+--------+------------------+-----------------+----------+------------+----+\n",
      "|_id|district_id| district_name|      neigh_name|Amount|PerMeter|        diffAmount|     diffPerMeter|usedAmount|usedPerMeter|year|\n",
      "+---+-----------+--------------+----------------+------+--------+------------------+-----------------+----------+------------+----+\n",
      "| 32|          7|Horta-Guinardó|el Baix Guinardó| 176.4|  2331.7|             237.1|           2987.8|     170.7|      2270.0|2013|\n",
      "| 32|          7|Horta-Guinardó|el Baix Guinardó| 201.1|  2546.2|             213.7|           2971.7|     200.5|      2528.9|2014|\n",
      "| 32|          7|Horta-Guinardó|el Baix Guinardó| 225.7|  3052.2|             302.9|           4301.5|     223.2|      3010.8|2015|\n",
      "| 32|          7|Horta-Guinardó|el Baix Guinardó| 222.8|  2991.0|296.48952380952386|3277.741232227488|     222.9|      2994.9|2016|\n",
      "| 32|          7|Horta-Guinardó|el Baix Guinardó| 272.5|  3552.0|             385.6|           4065.2|     265.1|      3518.3|2017|\n",
      "| 33|          7|Horta-Guinardó|        Can Baró| 192.6|  1823.4|296.48952380952386|3277.741232227488|     192.6|      1823.4|2013|\n",
      "| 33|          7|Horta-Guinardó|        Can Baró| 178.3|  2118.8|296.48952380952386|3277.741232227488|     178.3|      2118.8|2014|\n",
      "| 33|          7|Horta-Guinardó|        Can Baró| 129.1|  2101.1|296.48952380952386|3277.741232227488|     129.1|      2101.1|2015|\n",
      "| 33|          7|Horta-Guinardó|        Can Baró| 164.9|  2157.1|296.48952380952386|3277.741232227488|     164.9|      2157.1|2016|\n",
      "| 33|          7|Horta-Guinardó|        Can Baró| 221.3|  2423.7|296.48952380952386|3277.741232227488|     221.3|      2423.7|2017|\n",
      "+---+-----------+--------------+----------------+------+--------+------------------+-----------------+----------+------------+----+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "file_path = \"./Exploitation Zone/price_opendata.parquet\"\n",
    "\n",
    "df = spark.read.parquet(file_path)\n",
    "\n",
    "df.show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6f7bd771-37d0-40e1-9949-4654f03689f3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- _id: long (nullable = true)\n",
      " |-- district_id: long (nullable = true)\n",
      " |-- district_name: string (nullable = true)\n",
      " |-- neigh_name: string (nullable = true)\n",
      " |-- Amount: double (nullable = true)\n",
      " |-- PerMeter: double (nullable = true)\n",
      " |-- diffAmount: double (nullable = true)\n",
      " |-- diffPerMeter: double (nullable = true)\n",
      " |-- usedAmount: double (nullable = true)\n",
      " |-- usedPerMeter: double (nullable = true)\n",
      " |-- year: long (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a57a45c6-ee7b-431f-8cde-b983c335edd4",
   "metadata": {},
   "source": [
    "### 3. Comparative Analysis：Comparison of the latest annual prices in different neighborhoods."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "72157121-151a-49ae-8f79-4e862825c058",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import col, max, avg\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d9d56c84-1a77-440e-b9f8-4154c2772340",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2017"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "latest_year = df.select(max(\"year\")).collect()[0][0]\n",
    "latest_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "23b1861a-f8b8-4f27-a705-ddf5bf09f356",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_latest_year = df.filter(col(\"year\") == latest_year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "cbad97c6-b80d-458a-848a-edf10a96e29e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_avg_price_latest_year = df_latest_year.groupBy(\"neigh_name\") \\\n",
    "    .agg(avg(\"PerMeter\").alias(\"avg_PerMeter\")) \\\n",
    "    .orderBy(\"neigh_name\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "35054a9b-7da0-4676-b721-56f1143f1d46",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_df_avg_price_latest_year = df_avg_price_latest_year.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "5088fc95-e84a-47a8-b54b-d17a71f01ba5",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'plt' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[10], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m \u001b[43mplt\u001b[49m\u001b[38;5;241m.\u001b[39mfigure(figsize\u001b[38;5;241m=\u001b[39m(\u001b[38;5;241m14\u001b[39m, \u001b[38;5;241m8\u001b[39m))\n\u001b[0;32m      2\u001b[0m plt\u001b[38;5;241m.\u001b[39mbar(pd_df_avg_price_latest_year[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mneigh_name\u001b[39m\u001b[38;5;124m'\u001b[39m], pd_df_avg_price_latest_year[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mavg_PerMeter\u001b[39m\u001b[38;5;124m'\u001b[39m])\n\u001b[0;32m      3\u001b[0m plt\u001b[38;5;241m.\u001b[39mtitle(\u001b[38;5;124mf\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mAverage Price Per Square Meter in \u001b[39m\u001b[38;5;132;01m{\u001b[39;00mlatest_year\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m by Neighborhood\u001b[39m\u001b[38;5;124m'\u001b[39m)\n",
      "\u001b[1;31mNameError\u001b[0m: name 'plt' is not defined"
     ]
    }
   ],
   "source": [
    "plt.figure(figsize=(14, 8))\n",
    "plt.bar(pd_df_avg_price_latest_year['neigh_name'], pd_df_avg_price_latest_year['avg_PerMeter'])\n",
    "plt.title(f'Average Price Per Square Meter in {latest_year} by Neighborhood')\n",
    "plt.xlabel('Neighborhood')\n",
    "plt.ylabel('Average Price Per Square Meter (PerMeter)')\n",
    "plt.xticks(rotation=90)\n",
    "plt.grid(True)\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "658e7a61-1bbf-46ff-b5e7-9f5233d2c33d",
   "metadata": {},
   "source": [
    "### 4. Correlation Analysis: Price and Transaction Volume\n",
    "\n",
    "Investigate the correlation between average prices per square meter and total transaction amounts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "241e3550-0a48-4e67-8be0-5ac961eec736",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a7d9c82-dcbd-4e36-98ae-457e35fbb6c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f40733a-ba21-4acc-92ff-2f942684a6a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compute average price per square meter for each neighborhood\n",
    "df_avg_price = df.groupBy(\"neigh_name\") \\\n",
    "    .agg(avg(\"PerMeter\").alias(\"avg_PerMeter\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0bf6fd25-403c-44b7-844c-6e22d61686c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compute total transaction amount for each neighborhood\n",
    "df_total_amount = df.groupBy(\"neigh_name\") \\\n",
    "    .agg(sum(\"Amount\").alias(\"total_Amount\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "366fef21-ce73-4a97-ae81-89b721d604a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge the results into a single DataFrame\n",
    "df_merged = df_avg_price.join(df_total_amount, on=\"neigh_name\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "47577bb1-93c8-4589-accf-74aba68c3a5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert the result to a Pandas DataFrame\n",
    "pd_df = df_merged.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e9e8eff-adde-47ce-9a37-9c4755b7769a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate the correlation coefficient\n",
    "correlation = pd_df['avg_PerMeter'].corr(pd_df['total_Amount'])\n",
    "print(f\"Correlation between Average Price Per Square Meter and Total Transaction Amount: {correlation}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c1bec84-0bfc-4708-b43a-af86aa2ba5ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6bbed89-d417-459a-899a-bfee65b11aa8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Visualize the correlation\n",
    "plt.figure(figsize=(10, 6))\n",
    "sns.scatterplot(x='avg_PerMeter', y='total_Amount', data=pd_df)\n",
    "plt.title('Correlation between Average Price Per Square Meter and Total Transaction Amount')\n",
    "plt.xlabel('Average Price Per Square Meter (PerMeter)')\n",
    "plt.ylabel('Total Transaction Amount')\n",
    "plt.grid(True)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2775a16-27f4-4355-b185-e82f44c15fa0",
   "metadata": {},
   "source": [
    "### 5. Price Distribution: Analyze the distribution of average prices per square meter across neighborhoods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35fde2d1-9ad1-4223-98b4-1388d9a8f116",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compute average price per square meter for each neighborhood\n",
    "df_avg_price = df.groupBy(\"neigh_name\") \\\n",
    "    .agg(avg(\"PerMeter\").alias(\"avg_PerMeter\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7e67f0e-b3b5-4460-bc8c-65c95f68103e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert the result to a Pandas DataFrame\n",
    "pd_df_avg_price = df_avg_price.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "205f1930-fc8d-4408-b261-27da447b3513",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Visualize the price distribution\n",
    "plt.figure(figsize=(14, 7))\n",
    "sns.histplot(pd_df_avg_price['avg_PerMeter'], kde=True, bins=30)\n",
    "plt.title('Distribution of Average Price Per Square Meter Across Neighborhoods')\n",
    "plt.xlabel('Average Price Per Square Meter (PerMeter)')\n",
    "plt.ylabel('Frequency')\n",
    "plt.grid(True)\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef6542c7-2e0e-4014-bfc1-6866e0829e83",
   "metadata": {},
   "source": [
    "### 6. interactive dashboards\n",
    "\n",
    "- using `pip install ipywidgets` to install lib\n",
    "- using `pip install seaborn` to install lib"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db02d10f-cb08-448c-8bf2-7e369fea6585",
   "metadata": {},
   "source": [
    "#### 6.1 plot function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d3bf0d2-1c3e-4891-b6b8-7bd7600c5242",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to plot price trends for a selected neighborhood\n",
    "def plot_price_trend(neighborhood):\n",
    "    sns.lineplot(x='year', y='PerMeter', data=data[data['neigh_name'] == neighborhood], marker='o')\n",
    "    plt.title(f'Price Trend for {neighborhood}')\n",
    "    plt.xlabel('Year')\n",
    "    plt.ylabel('Average Price Per Square Meter (PerMeter)')\n",
    "    plt.grid(True)\n",
    "    plt.show()\n",
    "\n",
    "# Function to plot price distribution for a selected year\n",
    "def plot_price_distribution(year):\n",
    "    sns.histplot(data[data['year'] == year]['PerMeter'], kde=True, bins=30)\n",
    "    plt.title(f'Distribution of Average Price Per Square Meter in {year}')\n",
    "    plt.xlabel('Average Price Per Square Meter (PerMeter)')\n",
    "    plt.ylabel('Frequency')\n",
    "    plt.grid(True)\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "369605ff-aea3-47e1-ae82-7a1383b2aa14",
   "metadata": {},
   "source": [
    "#### 6.2 widgets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78167047-ab55-4edb-8811-3c580439cb9e",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = df.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bce460b1-b0f1-49e3-a45e-effee498a249",
   "metadata": {},
   "outputs": [],
   "source": [
    "import ipywidgets as widgets\n",
    "from ipywidgets import interact, interact_manual"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff5fd3a3-e85c-4c96-92fa-3a811bdf6d7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Interactive widgets\n",
    "neighborhoods = data['neigh_name'].unique()\n",
    "years = data['year'].unique()\n",
    "\n",
    "# Create dropdown widgets\n",
    "neigh_dropdown = widgets.Dropdown(\n",
    "    options=neighborhoods,\n",
    "    description='Neighborhood:'\n",
    ")\n",
    "\n",
    "year_slider = widgets.IntSlider(\n",
    "    value=years.min(),\n",
    "    min=years.min(),\n",
    "    max=years.max(),\n",
    "    step=1,\n",
    "    description='Year:'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b4e12da-cb62-45d6-803c-d17c2ceba363",
   "metadata": {},
   "source": [
    "#### 6.3 create interactive dashboard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ec0b9e3-c649-4937-89a6-72a4b68b84c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create interactive plots\n",
    "interact(plot_price_trend, neighborhood=neigh_dropdown)\n",
    "interact(plot_price_distribution, year=year_slider)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
